﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using DataLib.DBUtility;

namespace DataLib.DAL
{
	/// <summary>
	/// 数据访问类:VehicleArrange
	/// </summary>
	public partial class VehicleArrange
	{
		public VehicleArrange()
		{}
        #region  BasicMethod

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("id", "VehicleArrange");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int? id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from VehicleArrange");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(DataLib.Model.VehicleArrange model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into VehicleArrange(");
            strSql.Append("vehicleNo,platenumberid,vehiderid,cjoid,status,outid,sealno,Platecate,cumcode,cumname,platenumber,vehider)");
            strSql.Append(" values (");
            strSql.Append("@vehicleNo,@platenumberid,@vehiderid,@cjoid,@status,@outid,@sealno,@Platecate,@cumcode,@cumname,@platenumber,@vehider)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
                    new SqlParameter("@vehicleNo", SqlDbType.VarChar,20),
					new SqlParameter("@platenumberid", SqlDbType.NVarChar,100),
					new SqlParameter("@vehiderid", SqlDbType.NVarChar,100),
					new SqlParameter("@cjoid", SqlDbType.Int,4),
					new SqlParameter("@status", SqlDbType.Int,4),
					new SqlParameter("@outid", SqlDbType.Int,4),
					new SqlParameter("@sealno", SqlDbType.VarChar,2000),
					new SqlParameter("@Platecate", SqlDbType.Int,4),
					new SqlParameter("@cumcode", SqlDbType.VarChar,200),
					new SqlParameter("@cumname", SqlDbType.VarChar,500),
					new SqlParameter("@platenumber", SqlDbType.VarChar,500),
					new SqlParameter("@vehider", SqlDbType.VarChar,500)
                                        };
            model.vehicleNo = new tab_Num().GenNo("W", "PlateNo");
            parameters[0].Value = model.vehicleNo;
            parameters[1].Value = model.platenumberid;
            parameters[2].Value = model.vehiderid;
            parameters[3].Value = model.cjoid;
            parameters[4].Value = model.status;
            parameters[5].Value = model.outid;
            parameters[6].Value = model.sealno;
            parameters[7].Value = model.Platecate;
            parameters[8].Value = model.cumcode;
            parameters[9].Value = model.cumname;
            parameters[10].Value = model.platenumber;
            parameters[11].Value = model.vehider;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(DataLib.Model.VehicleArrange model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update VehicleArrange set ");
            strSql.Append("platenumberid=@platenumberid,");
            strSql.Append("vehiderid=@vehiderid,");
            strSql.Append("cjoid=@cjoid,");
            strSql.Append("status=@status,");
            strSql.Append("outid=@outid,");
            strSql.Append("sealno=@sealno,");
            strSql.Append("Platecate=@Platecate,");
            strSql.Append("cumcode=@cumcode,");
            strSql.Append("cumname=@cumname,");
            strSql.Append("platenumber=@platenumber,");
            strSql.Append("vehider=@vehider");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@platenumberid", SqlDbType.NVarChar,100),
					new SqlParameter("@vehiderid", SqlDbType.NVarChar,100),
					new SqlParameter("@cjoid", SqlDbType.Int,4),
					new SqlParameter("@status", SqlDbType.Int,4),
					new SqlParameter("@outid", SqlDbType.Int,4),
					new SqlParameter("@sealno", SqlDbType.VarChar,2000),
					new SqlParameter("@Platecate", SqlDbType.Int,4),
					new SqlParameter("@cumcode", SqlDbType.VarChar,200),
					new SqlParameter("@cumname", SqlDbType.VarChar,500),
					new SqlParameter("@platenumber", SqlDbType.VarChar,1000),
					new SqlParameter("@vehider", SqlDbType.VarChar,1000),
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = model.platenumberid;
            parameters[1].Value = model.vehiderid;
            parameters[2].Value = model.cjoid;
            parameters[3].Value = model.status;
            parameters[4].Value = model.outid;
            parameters[5].Value = model.sealno;
            parameters[6].Value = model.Platecate;
            parameters[7].Value = model.cumcode;
            parameters[8].Value = model.cumname;
            parameters[9].Value = model.platenumber;
            parameters[10].Value = model.vehider;
            parameters[11].Value = model.id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int? id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from VehicleArrange ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from VehicleArrange ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataLib.Model.VehicleArrange GetModel(int? id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 id,vehicleNo, platenumberid,vehiderid,cjoid,status,outid,sealno,Platecate,platenumber,cumcode,cumname,cumname,vehider from VehicleArrange ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            DataLib.Model.VehicleArrange model = new DataLib.Model.VehicleArrange();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataLib.Model.VehicleArrange DataRowToModel(DataRow row)
        {
            DataLib.Model.VehicleArrange model = new DataLib.Model.VehicleArrange();
            if (row != null)
            {
                if (row["id"] != null && row["id"].ToString() != "")
                {
                    model.id = int.Parse(row["id"].ToString());
                }
                if (row["platenumberid"] != null)
                {
                    model.platenumberid = row["platenumberid"].ToString();
                }
                if (row["vehiderid"] != null)
                {
                    model.vehiderid = row["vehiderid"].ToString();
                }
                if (row["cjoid"] != null && row["cjoid"].ToString() != "")
                {
                    model.cjoid = int.Parse(row["cjoid"].ToString());
                }
                if (row["status"] != null && row["status"].ToString() != "")
                {
                    model.status = int.Parse(row["status"].ToString());
                }
                if (row["outid"] != null && row["outid"].ToString() != "")
                {
                    model.outid = int.Parse(row["outid"].ToString());
                }
                if (row["vehicleno"] != null)
                {
                    model.vehicleNo = row["vehicleno"].ToString();
                }
                
                if (row["sealno"] != null)
                {
                    model.sealno = row["sealno"].ToString();
                }
                if (row["Platecate"] != null && row["Platecate"].ToString() != "")
                {
                    model.Platecate = int.Parse(row["Platecate"].ToString());
                }
                if (row["cumcode"] != null && row["cumcode"].ToString() != "")
                {
                    model.cumcode =row["cumcode"].ToString();
                }
                if (row["cumname"] != null && row["cumname"].ToString() != "")
                {
                    model.cumname = row["cumname"].ToString();
                }
                if (row["platenumber"] != null && row["platenumber"].ToString() != "")
                {
                    model.platenumber = row["platenumber"].ToString();
                }
                if (row["vehider"] != null && row["vehider"].ToString() != "")
                {
                    model.vehider = row["vehider"].ToString();
                }
            }
            return model;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,vehicleNo,platenumberid,vehiderid,cjoid,status,outid,sealno,Platecate,cumcode,platenumber,cumname,vehider ");
            strSql.Append(" FROM VehicleArrange ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        public DataTable GetListByCity(string city)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,vehicleNo,platenumberid,vehiderid,cjoid,status,outid,sealno,Platecate,cumcode,cumname,cumname,vehider,platenumber ");
            strSql.Append(",CASE cjoid when 0 then OutDepart else graphiccity END gcity ");
            strSql.Append("from (");
            strSql.Append("select va.id,platenumberid,vehiderid,cjoid,");
            strSql.Append("va.status,outid,sealno,Platecate,");
            strSql.Append("c.graphiccity,outs.OutDepart ");
            strSql.Append("FROM VehicleArrange va ");
            strSql.Append(" left join Cjo c on va.cjoid = c.id");
            strSql.Append(" left join OutStorage outs on va.outid = outs.id");
            strSql.Append(" where va.status=1 ");
            strSql.Append(" and (c.graphiccity like '%" + city + "%' ");
            strSql.Append("	or outs.OutDepart like '%" + city + "%'");
            strSql.Append(")) temp");

            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" id,vehicleNo,platenumberid,vehiderid,cjoid,status,outid,sealno,Platecate,cumcode,cumname,cumname,vehider ");
            strSql.Append(" FROM VehicleArrange ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM VehicleArrange ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperSQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.id desc");
            }
            strSql.Append(")AS Row, T.*  from VehicleArrange T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "VehicleArrange";
            parameters[1].Value = "id";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  BasicMethod
		#region  ExtensionMethod
        /// <summary>
        /// 根据ＣＪＯ单得到一个对象
        /// </summary>
        public DataLib.Model.VehicleArrange GetModelByCjoid(int cjoid)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 id,vehicleNo,platenumberid,vehiderid,cjoid,sealno,outid,Platecate,status,cumcode,cumname,cumname,vehider,platenumber from VehicleArrange ");
            strSql.Append(" where cjoid=@cjoid  order by id desc");
            SqlParameter[] parameters = {
					new SqlParameter("@cjoid", SqlDbType.Int,4)
			};
            parameters[0].Value = cjoid;

            DataLib.Model.VehicleArrange model = new DataLib.Model.VehicleArrange();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataLib.Model.VehicleArrange GetModelByOutid(int outid)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 id,vehicleNo,platenumberid,vehiderid,cjoid,sealno,outid,Platecate,status,cumcode,cumname,cumname,vehider,platenumber from VehicleArrange ");
            strSql.Append(" where outid=@outid");
            SqlParameter[] parameters = {
					new SqlParameter("@outid", SqlDbType.Int,4)
			};
            parameters[0].Value = outid;

            DataLib.Model.VehicleArrange model = new DataLib.Model.VehicleArrange();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 获取派车列表
        /// </summary>
        /// <param name="bcity">归属地</param>
        /// <param name="gcity">处理地</param>
        /// <returns></returns>
        public DataTable GetSendCarList(String bcity,String gcity)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@bcity",SqlDbType.VarChar,20),
                new SqlParameter("@gcity",SqlDbType.VarChar,20)

                                         };
            sqlparameter[0].Value = bcity;
            sqlparameter[1].Value = gcity;

            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GetSendCarList", sqlparameter);
            dt.Load(dr);
            dr.Close();     
            
            return dt;
        }

        public DataTable GetSendCarListStore(string bcity, string gcity, int currpage, int length,out int totalcount, string search)
        {
            DataTable dt = new DataTable();
            IDataParameter[] sqlparameter ={
                new SqlParameter("@bcity",SqlDbType.VarChar,20),
                new SqlParameter("@gcity",SqlDbType.VarChar,20),
                new SqlParameter("@currpage",SqlDbType.Int,4),
                new SqlParameter("@Length",SqlDbType.Int,4),
                new SqlParameter("@serach",SqlDbType.VarChar,155),
                new SqlParameter("@totalcount",SqlDbType.Int,4)
                                         };
            sqlparameter[0].Value = bcity;
            sqlparameter[1].Value = gcity;
            sqlparameter[2].Value = currpage;
            sqlparameter[3].Value = length;
            sqlparameter[4].Value = search;
            sqlparameter[5].Direction = ParameterDirection.Output;
            SqlDataReader dr = DbHelperSQL.RunProcedure("GetSendCarListSotre", sqlparameter);
            dt.Load(dr);
            dr.Close();
            totalcount = Convert.ToInt32(sqlparameter[5].Value);
            return dt;           
        }


		#endregion  ExtensionMethod
	}
}

